package org.cnbi.dao.base;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.cnbi.utils.db.SQLServer2005Dialect;
import org.cnbi.utils.dynamic.DynamicBeanUtil;
import org.cnbi.utils.pojo.DynamicBean;
import org.cnbi.utils.string.StringUtil;
import org.cnbi.utils.tools.Constants;
import org.cnbi.utils.tools.SymbolConstants;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
/**
 * 
 *  凡是dao层的方法,大家一定要严格遵守这种定义：
 *       add 添加方法的开头 
 *       update 更新方法的开头 
 *       del 删除方法的开头 
 *       query 查询方法的开头
 * 2014中国经邦. All rights reserved.   Copyright ©
 * @Author 龚佳新
 * @Time 2014年3月19日上午9:02:08
 */
public class BaseDao {
	protected static  final Log logger = LogFactory.getLog(BaseDao.class);
	
	
	private static String [] sqlType = {"Microsoft SQL Server","HSQL Database Engine","MySQL","Oracle"};
	
	
	@Autowired
	protected NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	@Autowired
	protected DataSource dataSource;

	/**
	 * 
	 * @param sql
	 *            语句
	 * @param paramMap
	 *            参数map
	 * @param obj
	 *            List里放的对象类型
	 * @return list
	 */
	@SuppressWarnings({ "unchecked", "unused" })
	private List<?> query(String sql, Map paramMap, Object obj)  {
		return namedParameterJdbcTemplate.query(sql, paramMap, new BeanPropertyRowMapper(obj.getClass()));
	}
	
	public List<?> query(String sql, Object object, Object paramObj)  {
		if(paramObj instanceof Map){
			return  query(sql, paramObj,object);
		}else{
			if (null != object) {
				Map paramMap = getParameterMap(sql, object);
				sql = sqlParamsReplace(sql, paramMap);
			}
			return query(sql, object);
		}
	}

	/**
	 * 可以是更新，删除与添加
	 * 
	 * @param sql
	 * @param obj
	 */
	public void execute(String sql, Object obj)  {
		if (null != obj) {
			if (obj instanceof Map) {
				namedParameterJdbcTemplate.update(sql, (Map)obj);
			} else {
				BeanPropertySqlParameterSource paramSource = new BeanPropertySqlParameterSource(obj);
				namedParameterJdbcTemplate.update(sql, paramSource);
			}
		} else {
			namedParameterJdbcTemplate.getJdbcOperations().update(sql);
		}
	

	}

	/**
	 * 查询记录数
	 * 
	 * @param sql
	 * @return
	 */
	public int queryCount(String sql,Object obj)  {
		if (null != obj) {
			if (obj instanceof Map) {
				return namedParameterJdbcTemplate.queryForInt(sql, (Map)obj);
			} else {
				BeanPropertySqlParameterSource paramSource = new BeanPropertySqlParameterSource(obj);
				return namedParameterJdbcTemplate.queryForInt(sql, paramSource);
			}
		} else {
			 return namedParameterJdbcTemplate.getJdbcOperations().queryForInt(sql);
		}
	}
	
	public BigDecimal queryBigDicamal(String sql,Object obj){
		if (null != obj){
			sql = sqlParamsReplace(sql, getParameterMap(sql, obj));
		}
		BigDecimal data = namedParameterJdbcTemplate.query(sql,  new ResultSetExtractor<BigDecimal>(){
		           @Override
		           public BigDecimal extractData(ResultSet rs) throws SQLException, DataAccessException {
		               while(rs.next()) {
		            	    return rs.getBigDecimal(1);
		               }
		              return new BigDecimal(0.00);
		           }
	           });
		return data;
	}
	protected Map getParameterMap(String sql, Object obj){
		sql = sql + " ";
		if (obj instanceof Map) {
			return (Map) obj;
		}
		Map paramMap = new HashMap();
		Method method = null;
		List<String> paramName = null;
		Class<Object> clazz = (Class<Object>) obj.getClass();
		paramName = getParamName(sql);
		try {
			for (String s : paramName) {
				String methodName = "get" + StringUtil.toUpperCaseFirstOne(s);
				method = clazz.getDeclaredMethod(methodName);
				paramMap.put(s, method.invoke(obj));
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		return paramMap;
	}
	/**
	 * 获取SQL参数List
	 * @param sql
	 * @return
	 * @Author 龚佳新
	 * @Time 2014年3月14日上午8:51:09
	 */
	public static List<String> getParamName(String sql){
		List<String> list = new LinkedList<String>();
		Pattern pt = Pattern.compile(":", Pattern.CASE_INSENSITIVE);
		Matcher mc = pt.matcher(sql);
		mc.find();
		mc.reset();
		int i = 0;
		while (mc.find()) {
			int index = sql.indexOf(":", i);
			String item = sql.substring(index + 1, sql.indexOf(" ", index));

			list.add(item.replace(")", ""));
			i = index + 1;

		}
		return list;
	}

	/**
	 * 功能：sql参数替换
	 * 由于暂时没有发现springMVC参数替换在哪个方法里，故自己写的参数替换方法,注意：如果参数值长度在在于1且为string类型：则前后‘不要：如
	 * ：dgd','454','op
	 * 
	 * @zhh 2014/2/17 将方法修改为 静态方法
	 * @param sql
	 * @param paramMap
	 * @return
	 */
	
	
	protected static String sqlParamsReplace(String sql, Map paramMap) {
		String dimspit = Constants.DIM+SymbolConstants.UNDERLINE;
		Set set = paramMap.keySet();
		Iterator it = set.iterator();
		while (it.hasNext()) {
			Object key = it.next();
			Object value = paramMap.get(key);

			if (value instanceof String) {//fact_
				String v = (String) value;
				if (key.toString().toLowerCase().contains("table")) {

				}else if(value.toString().contains(Constants.FACT)) {
				} else if(value.toString().contains("=")) {
				} else if(v.contains(SymbolConstants.COMMON)) {
					String[] varr = v.split("\\" + SymbolConstants.COMMON);
					StringBuffer vf = new StringBuffer("");
					for (int i = 0, len = varr.length; i < len; i++) {
						vf.append("'").append(varr[i]).append("'");
						if (i != len - 1) {
							vf.append(",");
						}
						value = vf.toString();
					}
				} else {
					if(!value.equals("")){
						value = "'" + (String) value + "'";
					}
				}
			} else if (key instanceof Integer) {
				value = (String) value;
			} else if (value instanceof Date) {
				value = "'" + value + "'";
			} else if (value instanceof Character) {
				value = "'" + value + "'";
			}
		
			sql = sql.replace(":" + key.toString(), value.toString());
		}
		return sql;
	}
	/**
	 * 此方法也是灵活的方法 可实现查询动态对象的集合
	 * 
	 * @param sql
	 * @param object
	 * @return
	 * @
	 * @Author 龚佳新
	 * @Time 2014年3月12日下午2:56:11
	 */
	public List<?> queryDynamicBeanList(String sql, Object object,boolean isDynamicBean) {
		List<DynamicBean> list = queryDynamicBeanList(sql, object);
		if(isDynamicBean == false){
			List<Object> resultList = new ArrayList<Object>();
			for(DynamicBean bean :list){
				resultList.add(bean.getObject());
			}
			return resultList;
		} 
		return list;
		
	}
	/**
	 * 动态List
	 * 
	 * @param sql
	 * @param obj
	 * @return
	 * @
	 * @Author 龚佳新
	 * @Time 2014年3月14日上午9:00:58
	 */
	public List<DynamicBean> queryDynamicBeanList(String sql, Object object)  {
		if (null != object) {
			Map paramMap = getParameterMap(sql, object);
			sql = sqlParamsReplace(sql, paramMap);
		}
		return namedParameterJdbcTemplate.getJdbcOperations().query(sql, new ResultSetExtractor<List<DynamicBean>>() {
			public List<DynamicBean> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ResultSetMetaData meta = rs.getMetaData();
				String[] label = getLabel(meta);
				List<DynamicBean> list = new ArrayList<DynamicBean>();
				while (rs.next()) {
					DynamicBean bean = DynamicBeanUtil.getDynamicBean(label,null);
					for (int i = 0; i < label.length; i++) {
						Object obj = rs.getObject(i + 1);
						handleObject(obj);
						if(obj instanceof BigDecimal)
							obj = obj.toString();
						bean.setValue(label[i], obj);
					}
					list.add(bean);
				}
				return list;
			}
		});
	}
	
	public List<Map> queryListMapBean(String sql, Object object)  {
		if (null != object) {
			Map paramMap = getParameterMap(sql, object);
			sql = sqlParamsReplace(sql, paramMap);
		}
		return namedParameterJdbcTemplate.getJdbcOperations().query(sql, new ResultSetExtractor<List<Map>>() {
			public List<Map> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ResultSetMetaData meta = rs.getMetaData();
				String[] label = getLabel(meta);
				List<Map> list = new ArrayList<Map>();
				while (rs.next()) {
					Map map = new LinkedHashMap();
					for (int i = 0; i < label.length; i++) {
						Object obj = rs.getObject(i + 1);
						handleObject(obj);
						map.put(label[i], obj);
					}
					list.add(map);
				}
				return list;
			}

		});
	}
	private String [] getLabel(ResultSetMetaData meta) throws SQLException{
		int columns = meta.getColumnCount();
		String[] label = new String[columns];
		for (int i = 0; i < label.length; i++) {
			label[i] = meta.getColumnName(i + 1);
		}
		return label;
	}
    private void handleObject(Object obj){
    	if (obj instanceof Long) {
			obj = String.valueOf(obj);
		} else if (obj instanceof Integer) {
			obj = String.valueOf(obj);
		} else if (obj instanceof BigDecimal) {
			BigDecimal bd = new BigDecimal(obj + "");
			obj = bd.doubleValue();
		} else if (obj instanceof Date) {
			obj = (Date) obj;
			obj = obj.toString();
		} else if (obj instanceof java.sql.Timestamp) {
			obj = (Timestamp) obj;
			obj = obj.toString();
		}
		if (obj instanceof Double) {
			DecimalFormat format = new DecimalFormat("00.########E0");
			obj = format.format((Double) obj);
		}
		if (null == obj) {
			obj = "0.00";
		}
    }
	public List<?> query(String sql, Object object)  {
		SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(object);
		List list = namedParameterJdbcTemplate.query(sql, sqlParameterSource, new BeanPropertyRowMapper(object.getClass()));
		return list;
	}

	public Object queryForObject(String sql, Object object)  {
		SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(object);
		return namedParameterJdbcTemplate.queryForObject(sql, sqlParameterSource, object.getClass());
	}
	
	/**
	 * 构造MySQL数据分页SQL
	 * @param queryString
	 * @param currentPage
	 * @param pageSize
	 * @return
	 */
	protected String getMySQLPageSQL(String queryString, Integer startIndex, Integer pageSize) {
		String result = "";
		if (null != startIndex && null != pageSize) {
			result = queryString + " limit " + startIndex + "," + pageSize;
		} else if (null != startIndex && null == pageSize) {
			result = queryString + " limit " + startIndex;
		} else {
			result = queryString;
		}
		return result;
	}
	/**
	 * 获取数据库类型方法  这咱方式以后会改的  有时间再弄吧！
	 * "Microsoft SQL Server",
	 * "HSQL Database Engine",
	 * "MySQL",
	 * "Oracle"
	 */ 
	 public String getDBType(){//
		 Connection conn = null;
		try {
			conn = dataSource.getConnection();
			DatabaseMetaData dbmd = conn.getMetaData();
			String dataBaseType = dbmd.getDatabaseProductName();
			if(sqlType[0].equals(dataBaseType)){
				return sqlType[0];
			}else if(sqlType[1].equals(dataBaseType)){
				return sqlType[1];
			}else if(sqlType[2].equals(dataBaseType)){
				return sqlType[2];
			}else if(sqlType[3].equals(dataBaseType)){
				return sqlType[3];
			}
		} catch (SQLException e) {
			e.printStackTrace();
			logger.error(e);
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error(e);
				e.printStackTrace();
			}
		}	 
		 return null;
	 }
	public String getPaginationSql(String queryString, Integer currentPage, Integer pageSize){
		String dbType = getDBType();
		if(sqlType[0].equals(dbType)){
			return getSqlserver2005PageSQL(queryString, currentPage, pageSize);
		}else if(sqlType[1].equals(dbType)){
			logger.error(sqlType[1]+"数据分页SQL还没写呢?");
		}else if(sqlType[2].equals(dbType)){
			return getMySQLPageSQL(queryString, currentPage, pageSize);
		}
		else if(sqlType[3].equals(dbType)){
			return getOraclePageSQL(queryString, currentPage, pageSize);
		}
		return getSqlserver2005PageSQL(queryString, currentPage, pageSize);
	}

	/**
	 * select top pageSize * from Sys_Logs
       where nid not in(select top pageSize*startIndex nid from Sys_Logs)
	 * 构造Sqlserver2005数据分页SQL
	 * @param queryString
	 * @param currentPage
	 * @param pageSize
	 * @return
	 */
	protected String getSqlserver2005PageSQL(String queryString, Integer currentPage, Integer pageSize) {
		String result = SQLServer2005Dialect.getLimitString(queryString, currentPage, pageSize);
		return result;
	}
	

	/**
	 * 构造Oracle数据分页SQL
	 * @param queryString
	 * @param startIndex
	 * @param pageSize
	 * @return
	 */
	protected String getOraclePageSQL(String sql, Integer startIndex, Integer pageSize) {
		  StringBuffer paginationSQL = new StringBuffer(" SELECT * FROM ( ");
		  paginationSQL.append(" SELECT temp.* ,ROWNUM num FROM ( ");
		  paginationSQL.append(sql);
		  paginationSQL.append(" ) temp where ROWNUM <= " + pageSize);
		  paginationSQL.append(" ) WHERE num > " + startIndex);
		return paginationSQL.toString();
	}
	
	/**
	 * 批量插入数据
	 * @param sql
	 * @param list 插入数据list集合
	 * @return int[] 
	 * **/
	public int[] batchUpdate(String sql, List<Object[]> list) {
		int[] row = namedParameterJdbcTemplate.getJdbcOperations().batchUpdate(sql, list);
		return row;
	}

}